package com.eleadmin.order.service.impl;

import cn.hutool.core.collection.CollUtil;
import com.eleadmin.common.core.config.ConfigProperties;
import com.eleadmin.common.core.utils.FileServerUtil;
import com.eleadmin.common.core.web.JsonResult;
import com.eleadmin.common.system.entity.User;
import com.eleadmin.common.system.service.UserRoleService;
import com.eleadmin.common.system.service.UserService;
import com.eleadmin.damon.utils.constant.UserConstant;
import com.eleadmin.damon.utils.results.PageResult2;
import com.eleadmin.damon.utils.utils.DateUtil;
import com.eleadmin.damon.utils.utils.PinyinUtils;
import com.eleadmin.damon.utils.utils.StringUtil;
import com.eleadmin.damon.utils.utils.Utils;
import com.eleadmin.order.biz.ContainerBillBiz;
import com.eleadmin.order.biz.ContainerBillFeeBiz;
import com.eleadmin.order.biz.UserBiz2;
import com.eleadmin.order.entity.ContainerBill;
import com.eleadmin.order.entity.ContainerBillFee;
import com.eleadmin.order.entity.Driver;
import com.eleadmin.order.param.ContainerBillParam;
import com.eleadmin.order.service.DriverService;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Service;
import com.eleadmin.order.biz.DriverBiz;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * 司机管理服务实现类
 *
 * @author : Doman
 * @version ：1.0
 * @date : 2023-04-07 13:41:05
 */
@Service
public class DriverServiceImpl /*extends ServiceImpl<DriverMapper, Driver> */ implements DriverService {

    @Autowired
    private DriverBiz driverBiz;
    @Autowired
    private UserBiz2 userBiz;
    @Autowired
    private UserService userService;
    @Autowired
    private UserRoleService userRoleService;
    @Autowired
    private ContainerBillFeeBiz containerBillFeeBiz;
    @Autowired
    private ContainerBillBiz containerBillBiz;

    @Override
    public JsonResult list(PageResult2<T> page, Driver driver, User loginUser) throws Exception {
        return driverBiz.list(page, driver, loginUser);
    }

    @Override
    public JsonResult list(Driver driver, User loginUser) {
        return JsonResult.ok().setData(driverBiz.list(driver, loginUser));
    }

    @Override
    public JsonResult listSelCarNo(Driver driver, User loginUser) {
        return JsonResult.ok().setData(driverBiz.listSelCarNo(driver, loginUser));
    }

    @Override
    public JsonResult listSelDriverName(Driver driver, User loginUser) {
        return JsonResult.ok().setData(driverBiz.listSelDriverName(driver, loginUser));
    }

    @Override
    public JsonResult getById(String id) {
        return JsonResult.ok().setData(driverBiz.getById(id));
    }

    @Transactional
    @Override
    public JsonResult save(Driver driver, User loginUser) {
        driver.setPinyin(PinyinUtils.ToFirstChar(driver.getName()));
        Integer id = driverBiz.save(driver, loginUser);
        if (Utils.isNotEmpty(driver.getAccount())) {
            User user = new User();
            user.setUsername(driver.getAccount());
            user.setPassword(userService.encodePassword(UserConstant.DEFAULT_PSW));
            user.setOriginPassword(UserConstant.DEFAULT_PSW);
            user.setType("司机");
            user.setNickname(driver.getName());
            user.setPhone(driver.getPhone());
            user.setCustomerId(id+"");
            user.setSex("1");
            Integer userid = userBiz.save(user, loginUser);
            List<Integer> roleIds = new ArrayList<>();
            roleIds.add(16);
            userRoleService.saveBatch(userid, roleIds);
        }
        return JsonResult.ok("添加成功");
    }

    @Transactional
    @Override
    public JsonResult update(Driver driver, User loginUser) {
        driver.setPinyin(PinyinUtils.ToFirstChar(driver.getName()));
        if (driverBiz.update(driver, loginUser)) {
            return JsonResult.ok("修改成功");
        }
        return JsonResult.error("修改失败");
    }

    @Transactional
    @Override
    public JsonResult remove(String id, User loginUser) {
        if (driverBiz.remove(id, loginUser)) {
            return JsonResult.ok("删除成功");
        }
        return JsonResult.error("删除失败");
    }




    //导出客户拖车账单
    @Transactional
    @Override
    public JsonResult exportsDriver(ContainerBill containerBill, User loginUser, HttpServletResponse response, HttpServletRequest request) throws Exception {
        File file = new File(getUploadDir(), "driver.xlsx");
        if (file.exists()) {
            file.delete();
        }
        containerBill.setDate1(containerBill.getDate1().substring(0, 10) + " 00:00");
        containerBill.setDate2(containerBill.getDate2().substring(0, 10) + " 23:59");
        List<ContainerBill> containerBillList = getContainerList(containerBill, loginUser);
        if (containerBillList != null && containerBillList.size() > 0) {
            ContainerBill containerBill1 = new ContainerBill();
            containerBill1.setDate1(containerBill.getDate1());
            containerBill1.setDate2(containerBill.getDate2());
            containerBill1.setType(containerBill.getType());
            List<String> feeNames = containerBillFeeBiz.feeNames(containerBill1, loginUser);
            if (feeNames != null && feeNames.size() > 0) {
                exportsDriverExcel(containerBillList, feeNames, containerBill);
                FileServerUtil.preview(file, true, "driver.xlsx", null, null, response, request);
            } else {
                try {
                    response.setContentType("text/html; charset=UTF-8");
                    response.getWriter().println("没有对应的数据哦,请再次查询!");
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return JsonResult.ok("文件已导出");
    }


    public List<ContainerBill> getContainerList(ContainerBill containerBill, User loginUser) {
        ContainerBillParam containerBillParam = new ContainerBillParam();
        containerBillParam.setFactoryPackDateSearch1(containerBill.getDate1());
        containerBillParam.setFactoryPackDateSearch2(containerBill.getDate2());
        containerBillParam.setCarNo(containerBill.getCarNo());
        List<ContainerBill> containerBillList = containerBillBiz.list(containerBillParam, loginUser);
        if (containerBillList != null && containerBillList.size() > 0) {
            for (int i = 0; i < containerBillList.size(); i++) {
                ContainerBill containerBillTemp = containerBillList.get(i);
                containerBillParam = new ContainerBillParam();
                if (StringUtil.isNotBlank(containerBill.getType())) {
                    containerBillParam.setType(containerBill.getType());//导出应收应付
                }
                if (StringUtil.isNotBlank(containerBillTemp.getId())) {
                    containerBillParam.setId(containerBillTemp.getId());
                }
                if (StringUtil.isNotBlank(containerBillTemp.getApplyId())) {
                    containerBillParam.setApplyId(containerBillTemp.getApplyId());
                }
//                if (StringUtil.isNotBlank(containerBillTemp.getCarNo())) {
//                    containerBillParam.setCarNo(containerBillTemp.getCarNo());
//                }
                List<ContainerBillFee> containerBillFeeList = containerBillFeeBiz.listForExport(containerBillParam, loginUser);
                containerBillTemp.setContainerBillFeeList(containerBillFeeList);
            }
        }
        return containerBillList;
    }

    public void exportsDriverExcel(List<ContainerBill> containerBillList, List<String> feeNames, ContainerBill containerBill) throws Exception {

        //https://www.cnblogs.com/janson071/p/10119935.html
        //https://www.cnblogs.com/pejsidney/p/8383843.html
        //创建工作簿
        XSSFWorkbook xssfWorkbook = null;
        xssfWorkbook = new XSSFWorkbook();

        //创建工作表
        XSSFSheet xssfSheet;
        xssfSheet = xssfWorkbook.createSheet();
        xssfSheet.setForceFormulaRecalculation(true);//公式不自动执行的问题

        //创建行
        XSSFRow xssfRow;

        //创建列，即单元格Cell
        XSSFCell xssfCell;

        xssfRow = xssfSheet.createRow(0);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格*

        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        Font fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 18); // 大小
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("宁波甩手掌柜供应链有限公司"); //设置单元格内容

        xssfRow = xssfSheet.createRow(1);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 12); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("Tel：0574-28820592"); //设置单元格内容

        xssfRow = xssfSheet.createRow(2);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 12); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        String to = containerBill.getDriver();
        xssfCell.setCellValue("To："+to); //设置单元格内容

        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(9+feeNames.size()-4); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        String date2 = "";
        if (Utils.isNotEmpty(containerBill.getDate2())) {
            date2 = " 到 "+containerBill.getDate2().substring(0, 10);
        }
        xssfCell.setCellValue("账期："+containerBill.getDate1().substring(0,10)+date2); //设置单元格内容

        List<String> headRow = CollUtil.newArrayList("序号","日期","提单号","箱型","箱号","工厂地址",
                "提箱场站","还箱场站","车牌号");
        headRow.addAll(feeNames);
        headRow.add("小计");
        xssfRow = xssfSheet.createRow(3);
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色
        xssfSheet.createFreezePane( 0, 4, 0, 0);

        String[] letters = new String[]{"J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z",
                "AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR",
                "AS","AT","AU","AV","AW","AX","AY","AZ","BA","BB","BC","BD","BE","BF","BG","BH","BI","BJ",
                "BK","BL","BM","BN","BO","BP","BQ","BR","BS","BT"};

        //把List里面的数据写到excel中
        for (int i = 0; i < containerBillList.size(); i++) {
            ContainerBill bill = containerBillList.get(i);
            List<ContainerBillFee> billFees = bill.getContainerBillFeeList();

            xssfRow = xssfSheet.createRow(i+4);
            //创建每个单元格Cell，即列的数据
            xssfCell = xssfRow.createCell(0); //创建单元格
            xssfCell.setCellValue(i+1); //设置单元格内容
            xssfCell = xssfRow.createCell(1); //创建单元格
            xssfCell.setCellValue(bill.getCustomer()); //设置单元格内容

            String add = "";
            if (bill.getFactoryAddress() != null && bill.getFactoryAddress().length() > 0) {
                JSONArray jsonArray = JSONArray.fromObject(bill.getFactoryAddress());
                for (int k=0; k<jsonArray.size(); k++) {
                    if(k != 0) {
                        add += ", \n";
                    }
                    JSONObject jsonObject = jsonArray.getJSONObject(k);
                    add += jsonObject.getString("factoryAddress").replaceAll("\t", "");
                }
            }

            List<String> row = CollUtil.newArrayList((i+1)+"", DateUtil.formatDate(DateUtil.parseDate(bill.getFactoryPackDate()+":00"),"MM-dd"),
                    bill.getBillOfLading(), bill.getBoxType(),
                    bill.getBoxNum(), add,
                    bill.getUpYard(), bill.getArrivalPort()=="null"?"":bill.getArrivalPort(), bill.getCarNo() );
            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
            fontStyle = xssfWorkbook.createFont(); // 字体样式
            fontStyle.setBold(false); // 加粗
            fontStyle.setFontName("宋体"); // 字体
            fontStyle.setFontHeightInPoints((short) 10); // 大小
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);

            float temp_ret = 0;
            for(int t = 0; t < feeNames.size(); t ++) {
                String feename = feeNames.get(t);
                temp_ret = 0;
                for (int j = 0; j < billFees.size(); j ++) {
                    ContainerBillFee bf = billFees.get(j);
                    if(bf.getFeeName().equals(feename)) {
                        if(Utils.isNotEmpty(bf.getPrice())) {
                            temp_ret += Float.parseFloat(bf.getPrice());
                        }
                    }
                }
                row.add(temp_ret+"");
            }
            row.add("function::SUM("+letters[0]+(i+5)+":"+letters[feeNames.size()-1]+(i+5)+")");

            setRow(xssfRow,row, cellStyle, ",9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,");
        }

        xssfRow = xssfSheet.createRow(4+containerBillList.size());
//        List<String> row = new ArrayList<>();
//        row.add("");row.add("");row.add("");row.add("");row.add("");
//        row.add("");row.add("");row.add("");row.add("");
//        setRowNoValue(xssfRow,row, cellStyle,"");
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("合计:"); //设置单元格内容


        for(int i =0; i <= feeNames.size(); i ++) {
            xssfCell = xssfRow.createCell(9+i); //创建单元格
            fontStyle = xssfWorkbook.createFont(); // 字体样式
            fontStyle.setFontName("宋体"); // 字体
            fontStyle.setFontHeightInPoints((short) 10); // 大小
            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellType(CellType.NUMERIC);
            xssfCell.setCellFormula("SUM("+letters[i]+"5:"+letters[i]+(containerBillList.size()+4)+")"); //设置单元格内容
        }

        // 合并单元格
        CellRangeAddress cra =new CellRangeAddress(0, 0, 0, 9+feeNames.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra =new CellRangeAddress(1, 1, 0, 9+feeNames.size()); // 起始行, 终止行, 起始列, 终止列

        xssfSheet.addMergedRegion(cra);
        cra =new CellRangeAddress(2, 2, 0, 9+feeNames.size()-5); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra =new CellRangeAddress(2, 2, 9+feeNames.size()-4, 9+feeNames.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra =new CellRangeAddress(4+containerBillList.size(), 4+containerBillList.size(), 0, 8); // 起始行, 终止行, 起始列, 终止列
        RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
        xssfSheet.addMergedRegion(cra);

        OutputStream os = new FileOutputStream(new File(getUploadDir() + "driver.xlsx"));
        //用输出流写到excel
        xssfWorkbook.write(os);
        os.flush();
        os.close();
    }


    public void setRow(XSSFRow xssfRow, List<String> row, XSSFCellStyle cellStyle, String changeNum) {
        //创建每个单元格Cell，即列的数据
        if (Utils.isNotEmpty(row)) {
            for (int i = 0; i < row.size(); i++) {
                XSSFCell xssfCell = xssfRow.createCell(i); //创建单元格
                String str = row.get(i);
                if (str == null) {
                    str = "";
                }
                //https://blog.csdn.net/weixin_29100399/article/details/114203311
                if (str.indexOf("function::") != -1) {
                    xssfCell.setCellFormula(str.substring(10, str.length())); //设置单元格内容
                } else if (changeNum.indexOf("," + i + ",") != -1) {
                    xssfCell.setCellType(CellType.NUMERIC);
                    if (Utils.isNotEmpty(row.get(i))) {
                        xssfCell.setCellValue(Double.valueOf(row.get(i))); //设置单元格内容
                    } else {
                        xssfCell.setCellValue(0);
                    }
                } else {
                    xssfCell.setCellValue(row.get(i)); //设置单元格内容
                }
                if (cellStyle != null) {
                    xssfCell.setCellStyle(cellStyle);
                }
            }
        }
    }

    @Resource
    private ConfigProperties config;

    /**
     * 文件上传基目录
     */
    private String getUploadBaseDir() {
        return File.listRoots()[config.getUploadLocation()].getAbsolutePath()
                .replace("\\", "/") + "/upload/";
    }

    /**
     * 文件上传位置
     */
    private String getUploadDir() {
        return getUploadBaseDir() + "file/";
    }
}
